<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
// Modification JTA au 10/05/2012
function doQueryRenduEtape0($conn,$altRefs)
{
    $queryString="select
        contrat.alternanceCle,
        etudiant.prenom as prenom_etud,
        etudiant.nom as nom_etud,
        entreprise.nom,
        etudiant.tel,
        etudiant.mail,
     
        membre.prenom, membre.nom as prof,
        membre.tel, membre.mail,

        referent.prenom, referent.nom ,
        referent.fonction,
        referent.tel,
        referent.mail,
        contrat.opcaRef,

        contrat.signatureContrat,
        contrat.accordOPCA,
        contrat.debutContrat,
        contrat.finContrat,

        membre.profCle,
        entreprise.entrepriseCle,
        contrat.bureauRef,
        etudiant.etudCle,
        etudiant.mailLille1,

        opca.nom,
        referent.referentCle,
        referent2.referentCle,
        regie.entrepriseRef,
        regieBureauRef,
        regieReferentRef,
        regieReferentRef2,
        concat(regie.adresse,' - ',regie.adresse),
        concat(bureau.adresse,' - ',bureau.ville),
        concat(referent.tel,' - ',referent.mail),
        concat(referent2.tel,' - ',referent2.mail),
        concat(regieReferent.tel,' - ',regieReferent.mail),
        concat(regieReferent2.tel,' - ',regieReferent2.mail),
        formationRef,
        notifAttribTuteur
    from
	(contrat inner join etudiant 
		on etudCle=etudRef inner join etudiant_groupe on etudiant_groupe.annee=contrat.anneeCle
      and etudiant_groupe.etudRef=etudiant.etudCle inner join groupe on groupe.groupeCle=etudiant_groupe.groupeRef) inner join
            referent on contrat.referentRef=referent.referentCle left join
            referent referent2 on contrat.referentRef2=referent2.referentCle left join
            referent regieReferent on contrat.regieReferentRef=regieReferent.referentCle left join
            referent regieReferent2 on contrat.regieReferentRef2=regieReferent2.referentCle left join
            membre on contrat.tuteurRef=membre.profCle inner join
            bureau on contrat.bureauRef=bureau.bureauCle
            left join bureau regie on regie.bureauCle=contrat.regieBureauRef inner join
            entreprise on bureau.entrepriseRef like entreprise.entrepriseCle
            inner join opca on contrat.opcaRef=opcaCle
        where alternanceCle in (" . $altRefs . ") order by etudiant_groupe.groupeRef, prof, nom_etud, prenom_etud ;";

    $result=mysql_query($queryString,$conn);
    error_log("querying ".str_replace("\n\t","  ",$queryString)." - ".mysql_error());
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    
    return $result;
}

function doQueryRenduEtape1($conn,$altRefs)
{
    $queryString="select
        contrat.alternanceCle,
        etudiant.prenom, etudiant.nom,
        etapeetudtut.dateRencontre,
        entreprise.nom,
        if (etapeetudtut.service is null,infoetud.service,etapeetudtut.service),
        if (etapeetudtut.client is null,infoetud.client,etapeetudtut.client),
        if (etapeetudtut.missions is null,infoetud.missions,etapeetudtut.missions),
        if (etapeetudtut.environnementTechnique is null,infoetud.environnementTechnique,etapeetudtut.environnementTechnique),
        etapeetudtut.integrationEntreprise,
        etapeetudtut.signatureEtud,etapeetudtut.remarquesEtud,
        etapeetudtut.signatureTuteur,etapeetudtut.remarquesTuteur,
        if (etapeetudtut.motscles is null,infoetud.motscles,etapeetudtut.motscles)
    from
	(contrat inner join etudiant
		on etudCle=etudRef inner join etudiant_groupe on etudiant_groupe.annee=contrat.anneeCle
      and etudiant_groupe.etudRef=etudiant.etudCle inner join groupe on groupe.groupeCle=etudiant_groupe.groupeRef) inner join
            referent on contrat.referentRef=referent.referentCle inner join
            bureau on contrat.bureauRef=bureau.bureauCle inner join
            entreprise on bureau.entrepriseRef=entreprise.entrepriseCle left join
            etapeetudtut on etapeetudtut.alternanceRef like alternanceCle left join
            infoetud on infoetud.alternanceRef like alternanceCle
        where alternanceCle in (" . $altRefs . ") order by etudiant_groupe.groupeRef, etudiant.nom;";

    $result=mysql_query($queryString,$conn);
    error_log("querying ".str_replace("\n\t","  ",$queryString)." - ".mysql_error());
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryRenduEtape2($conn,$altRefs)
{
    $queryString="select
        contrat.alternanceCle,
        etudiant.prenom, etudiant.nom,
        etapevisite1.dateRencontre,
        etapevisite1.adequationMission,
        etapevisite1.integrationEtudiant,
        etapevisite1.signatureEtud,etapevisite1.remarquesEtud,
        etapevisite1.signatureReferent,etapevisite1.remarquesReferent,
        etapevisite1.signatureTuteur,etapevisite1.remarquesTuteur,
        etudiant.mail,
        membre.mail,
        referent.mail,referent.prenom,referent.nom,
        membre.prenom,membre.nom,
        referent2.mail,referent2.prenom,referent2.nom,
        referentRegie.mail,referentRegie.prenom,referent2.nom,
        referentRegie2.mail,referentRegie2.prenom,referentRegie2.nom

    from
	(contrat inner join etudiant
		on etudCle=etudRef inner join etudiant_groupe on etudiant_groupe.annee=contrat.anneeCle
      and etudiant_groupe.etudRef=etudiant.etudCle inner join groupe on groupe.groupeCle=etudiant_groupe.groupeRef) left join
            etapevisite1 on etapevisite1.alternanceRef like alternanceCle
            inner join referent on contrat.referentRef=referentCle
            left join referent referent2 on contrat.referentRef2=referent2.referentCle
            left join referent referentRegie on contrat.regieReferentRef=referentRegie.referentCle
            left join referent referentRegie2 on contrat.regieReferentRef2=referentRegie2.referentCle
            inner join membre on contrat.tuteurRef=profCle
        where alternanceCle in (" . $altRefs . ") order by etudiant_groupe.groupeRef, etudiant.nom;";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    
    return $result;
}

function doQueryRenduEtapeMissionSoutenance($conn,$altRefs)
{
    $queryString="select
        contrat.alternanceCle,
        etudiant.prenom, etudiant.nom,
        etapemissionsout.dateRencontre,
        etapemissionsout.typeRencontre,
        etapemissionsout.dateValidation,
        etapemissionsout.missions,
        etapemissionsout.environnementTechnique,
        etapemissionsout.enjeux,
        etapemissionsout.signatureEtud,etapemissionsout.remarquesEtud,
        etapemissionsout.signatureReferent,etapemissionsout.remarquesReferent,
        etapemissionsout.signatureTuteur,etapemissionsout.remarquesTuteur,
        etudiant.mail,
        membre.mail,
        referent.mail,referent.prenom,referent.nom,
        membre.prenom,membre.nom,
        referent2.mail,referent2.prenom,referent2.nom
    from
	(contrat inner join etudiant
		on etudCle=etudRef inner join etudiant_groupe on etudiant_groupe.annee=contrat.anneeCle
      and etudiant_groupe.etudRef=etudiant.etudCle inner join groupe on groupe.groupeCle=etudiant_groupe.groupeRef) inner join
            membre on contrat.tuteurRef = membre.profCle inner join
            referent on contrat.referentRef=referent.referentCle left join
            referent referent2 on contrat.referentRef2=referent2.referentCle left join
            etapemissionsout on etapemissionsout.alternanceRef like alternanceCle
        where alternanceCle in (" . $altRefs . ") order by etudiant_groupe.groupeRef, etudiant.nom;";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0) error_log("querying ".$queryString." - ".mysql_error());
    return $result;
}

function doQueryRenduEtapeBilan($conn,$altRefs)
{
    //TODO : PB WITH QUERYING... WHEN ALTERNANCEREF=0
    $queryString="select
        contrat.alternanceCle,
        etudiant.prenom, etudiant.nom,
        groupe.formationRef,
        etapevisite2.dateRencontre,
        etapevisite2.pointsPositifs,
        etapevisite2.pointsProgres,
        etapevisite2.avancementProjet,
        etapevisite2.dateProbableSoutenance,
        etapevisite2.signatureEtud,etapevisite2.remarquesEtud,
        etapevisite2.signatureReferent,etapevisite2.remarquesReferent,
        etapevisite2.signatureTuteur,etapevisite2.remarquesTuteur,
        etudiant.mail,
        membre.mail,
        referent.mail,referent.prenom,referent.nom,
        membre.prenom,membre.nom,
        ref2.mail,ref2.prenom,ref2.nom
    from
	(contrat inner join etudiant
		on etudCle=etudRef inner join etudiant_groupe on etudiant_groupe.annee=contrat.anneeCle
      and etudiant_groupe.etudRef=etudiant.etudCle inner join groupe on groupe.groupeCle=etudiant_groupe.groupeRef) inner join
            
            membre on contrat.tuteurRef = membre.profCle inner join
            referent on contrat.referentRef=referent.referentCle left join
            referent ref2 on contrat.referentRef2=ref2.referentCle left join
            etapevisite2 on etapevisite2.alternanceRef like alternanceCle
        where alternanceCle in (" . $altRefs . ") order by etudiant_groupe.groupeRef, etudiant.nom;";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0)
     error_log("querying ".$queryString." - ".mysql_error());
    
    return $result;
}

function extraireNP($cleRef) {

  return substr($cleRef,0,strpos($cleRef,"_",strpos($cleRef,"_")+1));
}

?>
